Find assistant for creating database queries

ABSTRACT

A method, apparatus, and article of manufacture which provides for finding database management information on a computer in a simple, user-friendly way to minimize errors and maximize database usership. The invention displays a window containing a card metaphor in a graphical user interface on a computer display. The window allows the user to enter a find type and a find condition selected from a displayed menu. The window also displays a natural language description of the current status of the find request, allowing the user to see, in real time, the scope of the request and the databases affected.

CROSS REFERENCE TO RELATED APPLICATION

This application is related to the following co-pending andcommonly-assigned patent applications:

Application Ser. No. 08/707,140, "USER INTERFACE FOR VISUALLY DEPICTINGINNER/OUTER/LEFT/RIGHT JOINS IN A DATABASE SYSTEM," filed on same dateherewith, by Darryl J. Mocek et al., and

Application Ser. No. 08/707,139, entitled "NATURAL LANGUAGE TRANSLATIONOF AN SQL QUERY," filed on same date herewith, by Darryl J. Mocek etal.,

Both of which applications are incorporated by reference herein.

BACKGROUND OF THE INVENTION

1. Field of the Invention

This invention relates generally to methods of accessing informationstored in a database management system (DBMS), and in particular to acomputer-implemented user interface for creating DBMS queries.

2. Description of Related Art

Large-scale integrated database management systems provide an efficient,consistent, and secure means for storing and retrieving vast amounts ofdata. This ability to manage massive amounts of information is a virtualnecessity in business today.

Relational Database Management Systems (RDBMS) software using StructuredQuery Language (SQL) interfaces are well known in the art. The SQLinterface has evolved into a standard language for RDBMS software andhas been adopted as such by both the American National StandardsInstitute (ANSI) and the International Standards Organization (ISO).

In RDBMS software, all data is externally structured into tables. TheSQL interface allows users to formulate relational operations on thetables either interactively, in batch files, or embedded in hostlanguages such as C, COBOL, etc. Operators are provided in SQL thatallow the user to manipulate the data, wherein each operator operates oneither one or two tables and produces a new table as a result. The powerof SQL lies on its ability to link information from multiple tables orviews together to perform complex sets of procedures with a singlestatement.

Unfortunately, while powerful, the SQL interface can be difficult touse, especially for laypersons with little or no experience withdatabases or computers. This problem limits the application of RDBMStechnology to provide business information, often to those who need itthe most. Potential RDBMS users often fail to avail themselves to thebenefits of RDBMS technology because they are either intimidated by orreluctant to learn new technology. Even if they are willing to learn,expensive and lengthy training may be required before these potentialusers achieve a basic functional capability. SQL's idiosyncrasies alsohave other negative consequences. For example, both novice andexperienced SQL users often formulate improper SQL commands, and obtainsearch results which fail to disclose critical data or require the DBMSto search through more data than is required, lengthening the search,and placing unnecessary demands on DBMS processing.

SUMMARY OF THE INVENTION

To overcome the limitations in the prior art described above, and toovercome other limitations that will become apparent upon reading andunderstanding the present specification, the present invention disclosesa method, apparatus, and article of manufacture for displaying databasemanagement information on a computer. The method comprises the steps ofdisplaying a window containing a card metaphor on a computer displaydevice, accepting a find type and a find condition from a user inputdevice coupled to the computer, interpreting the user-selected findrequest to produce a natural language description of the request, andpresenting the natural language description of the command to the useron the display device. In an exemplary embodiment presented herein, theinformation displayed to the user includes a database table box having alist of database tables, an associated database field box having a listof database fields associated with the database tables, a relationalcondition box, and a search string edit box, in which the user may entera combination of search strings and logical relationships between thesearch strings.

The present invention therefore presents the user with a list ofselectable database tables, and fields associated with those tables, abox to enter search strings, and a list of selectable relationalconditions to apply to the database fields and search strings. Thepresent invention interprets the user-selected values for these items,and presents, on the same screen, a natural language description of thefind request. As a result, the user can read a plain languagedescription of the current database command while the information isentered, and before the search command is transmitted to the relationaldatabase management system. This averts potential database commanderrors and the resulting processing delays, while at the same time,encouraging novice users to use the RDBMS, and to experiment withdifferent SQL queries.

The present invention also provides SQL functionality by allowing theuser to construct search strings with logical relationships betweensearch parameters and to create complex SQL commands by linking simplerSQL commands with logical conditional operators.

BRIEF DESCRIPTION OF THE DRAWINGS

Referring now to the drawings in which like reference numbers representcorresponding parts throughout:

FIG. 1 illustrates an exemplary computer hardware environment that couldbe used with the present invention;

FIG. 2 is a diagram illustrating a type of find window compatible withthe present invention;

FIG. 3 is a diagram illustrating a conditions window compatible with thepresent invention;

FIG. 4 is a diagram illustrating a second conditions window compatiblewith the present invention;

FIG. 5 is a diagram illustrating a sort window compatible with thepresent invention;

FIG. 6 is a diagram illustrating a SQL window compatible with thepresent invention;

FIG. 7 is a diagram illustrating a name window compatible with thepresent invention; and

FIGS. 8A-8C are flow diagrams describing the operation of the presentinvention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT

In the following description of the preferred embodiment, reference ismade to the accompanying drawings which form a part hereof, and in whichis shown by way of illustration a specific embodiment in which theinvention may be practiced. It is to be understood that otherembodiments may be utilized and structural changes may be made withoutdeparting from the scope of the present invention.

Hardware Environment

FIG. 1 illustrates an exemplary computer hardware environment that couldbe used with the present invention. In the exemplary environment, acomputer system 100 comprises a computer 106 coupled to I/O devicescomprising a monitor 102, a keyboard 108, a mouse device 110, and aprinter 118. The computer 106 could also be coupled to other I/Odevices, including a local area network (LAN) or wide area network (WAN)via interface cable 120. The monitor 102 presents a display 104 visuallydepicting information from the computer system 100 to the user.

The computer 106 comprises a processor and a memory including randomaccess memory (RAM), read only memory (ROM), and/or other components.The computer 106 operates under control of an operating system 122stored in the memory to present data to the user on the display 104 andto accept and process commands from the user via keyboard 108 and mousedevice 110.

The present invention is preferably implemented using one or morecomputer programs or applications through a graphical user interface.These computer programs are depicted as windows 124 presented on thedisplay 104, operating under control of the operating system 122.

Generally, the operating system and the computer programs implementingthe present invention are tangibly embodied in a computer-readablemedium, e.g. one or more of removable data storage devices 112, 114,such as a zip or floppy disc drive, or fixed data storage devices 130,including for example, a hard drive, CD-ROM drive, or tape drive. Also,the relational databases used with the present invention can be storedin data storage devices 130, 112, 114, or may be stored off-line andaccessed via interface cable 120.

Those skilled in the art will recognize that the exemplary environmentillustrated in FIG. 1 is not intended to limit the present invention.Indeed, those skilled in the art will recognize that other alternativehardware environments may be used without departing from the scope ofthe present invention.

User Operation

The operation of the present invention is described with reference toFIG. 2 through FIG. 7, which illustrate exemplary embodiments of theuser interface aspects of the present invention.

Type of Find Card Panel

FIG. 2 presents a diagram of an exemplary embodiment of theuser-interface of the present invention. The user interface comprises atype of find window 300 presented on the display 104 of the computersystem 100, which includes a plurality of tabs 302-308 used to navigatethe user interface. The information shown in FIG. 2 is displayed whenthe user selects the type of find tab 302. This provides a list of theexisting find requests the user has previously stored in the named findbox 312. First, the user must indicate whether a new find request isbeing created or the user wishes to edit an existing find request byselecting from radio buttons 310. If the user selects create a new find,the condition window 318 appears, as shown in FIG. 3. The user canselect different find request types by selecting from the find type list314. The user can select on the next button 316 or the condition tab304, to proceed.

Condition Window

FIG. 3 shows the condition window of the present invention. Thecondition window 318 includes a database tables display box 320, adatabase fields box 322, field, field type, and field width of thecurrently chosen database field. The condition window 318 allows a userto select from a display box of database tables 320. Upon selecting adatabase table, a fields list 322 is displayed which allows the user toselect any of the fields defined for the currently selected table. Afind method 324 is selected by choosing an entry from the list of findmethods 324. The list of find methods 324 consists of a variety ofmethods, such as matches, does not match, contains the character(s), isbetween, starts with the character(s), ends with the character(s), isexactly equal to, is greater than (>), is greater than or equal to (>=),is less than (<), is less than or equal to (<=), and so on. It will berecognized that other find conditions may be used with the presentinvention without loss of generality.

The next selection the user makes is the find criteria of the search,displayed in the data to find box 326. The data to find box preferablyis initialized as a one column grid object with one row enabled. A usercan enter many values as the criteria in the find box 326 eitherhorizontally, separated by commas, or vertically. For example, as shownin FIG. 3, if the user wanted to find all records in the Customer tablewhere the State field was CA, HI and WA, the selection would be "State"from the fields list 322, "Matches" from the find methods list 324, and"CA", "HI", and "WA" entered in the data to find box 326. When the "C"in CA was entered by the user in the first row of the data to find box326, the second row in the grid object became enabled to allow the entryof more than one state. This is very useful in helping the user realizethat more than one value can be entered as a criterion at this point.

A natural language description corresponding to the find request isdisplayed in display box 328 as the user makes selections. It will benoted that these multiple entered values are related to the find requestthrough logical OR operators, as shown in the display box 328. A virtual"Done" button 336 is enabled during the selection process whichterminates the current session of the find assistant. The user canselect on the next button 334 or the sort tab 306, to proceed. If theuser wanted to find using values in another field, the "Find on anotherfield" button 330 moves to a new panel showing a second condition window338, as shown in FIG. 4.

Second Condition Window

FIG. 4 shows a second condition window of the present invention. Thesecond condition window 338 is similar to the condition window 318 shownin FIG. 3. The second condition window 338 allows a user to select adatabase table, a field from the table, a find method, and findcriteria. The user indicates by selecting from radio buttons 340 whetherthe new find condition is related to the existing condition via alogical AND or logical OR condition. A previously added condition may bedeleted by selecting the delete condition button 342.

The user can select on the next button 334 or the sort tab 306, toproceed, or the back button 332 to return to the previous window. The"Done" button 336 is enabled during the selection process whichterminates the current session of the find assistant.

Sort Window

FIG. 5 presents an illustration of sort window 344 of the presentinvention, which is selected by the sort tab 306. The sort window 344allows the user to sort data before viewing it on the display 104. Thesort window 344 contains a sort window database field listbox 322, whichlists all of the database fields in the user-selected database tables.Using the mouse device 110, the user can double click on any of thesefields or single click on the field and select the sort window addbutton 346 to move them to the "fields to sort" 354. Likewise, the usercan remove fields from the "fields to sort" listbox 354 by doubleclicking on the selections, or by single clicking on the selections andselecting the sort window remove button 348. All fields in the "fieldsto sort" listbox 354 can be cleared by selecting the sort window clearall button 350.

SQL Window

FIG. 6 presents an exemplary embodiment of the SQL window. The SQLwindow 356 is presented on the display 104 when the user selects the SQLtab 307.

The SQL window 356 comprises an SQL statement edit box 362, in which anSQL statement generated by the foregoing user input is displayed. TheSQL statement word wraps within the box automatically. If the SQLstatement is too long to view in the SQL statement edit box 362 in itsentirety, the statement is word-wrapped and scroll bars are appended tothe SQL statement edit box 362.

Each keyword in the SQL statement (such as SELECT, FROM, WHERE, ORDERBY) are placed on a new line to increase readability. Using the mousedevice 110, the user can select within this field and highlightinformation. If the user attempts to modify information in the SQLstatement edit box 362, a dialog box appears and warns the user that alltabs except the SQL tabs 307 will become disabled, and other aspects ofthe user interface described herein will no longer be available for use.The dialog box allows the user to proceed or return. If the userreturns, the SQL window 356 is again presented on the display 104, withno changes made. If the user proceeds, the SQL statement edit box 362will become active, and will allow manual SQL statement changes.

The SQL window 356 also comprises an SQL window database table dropdownlistbox 320 and an SQL window associated field listbox 322, which allowthe user to view the tables and fields and is provided primarily foruser reference when making editing changes to the SQL statement edit box362. If the user selects the SQL window database table dropdown listboxtab 320, a list of all database tables that are currently open andavailable for searching are displayed in the SQL window database tabledropdown listbox 320. When a database table is selected, the names ofthe fields associated with the selected database table are displayed inthe SQL window associated fields listbox 322. When the user selects afield, the database field name will be placed in the SQL statement editbox 362 at the cursor position. Alternatively, the user then may alsodrag a field name from the SQL window associated fields listbox 322 anddrop it into the SQL statement edit box 362. When the user is dropping afield name, an extended underscore (₋₋₋₋₋₋) will appear in the SQLstatement editbox 362 allowing the user to select where in the SQLstatement edit box 362 the fieldname will be placed. If this is thefirst item to be changed, the database field name is appended to the SQLstatement, and a dialog box appears to warn the user after themodifications have been made, the user must make any furthermodifications manually.

A feature of the present invention is described with reference to thenamed SQL statement indicator 358 and edit box 360. This allows the userto give a simple, plainlanguage name for frequently used searches,thereby making the user interface easier to use. For example, the usermay wish to name an SQL statement retrieving all customers in Californiawhose orders exceeded $10,000 by naming the SQL statement "GOOD₋₋ CAL."Thereafter, using this feature, the user can use the term "GOOD₋₋ CAL"in place of the equivalent SQL statement.

The user may also proceed directly to the SQL window 356 withoutentering information in the tab windows selected by tabs 302-308. Whenthis happens, the user can click the SQL statement box 362 and manuallyenter an SQL SELECT statement. Since no SQL statement was created, nowarning dialog box is required in this situation, however, as soon asthe user begins entering data into the SQL statement box 362 manually,all tabs except the SQL tab 307 are disabled. If the user deletes theentire manually entered SQL SELECT statement, all tabs 302-308 becomere-enabled.

After the SQL statement is completely entered, the user selects name tab308. At this time, the SQL statement is parsed and sent to the SQLdatabase. If the parser finds an error within the SQL statement, adialog box indicates as such to the user.

Name Window

FIG. 7 presents an exemplary embodiment of the name window. The namewindow 364 is presented on the display 104 when the user selects thename tab 308. A natural language description corresponding to the findrequest is displayed in display box 328. The find request can be namedby using the mouse 110 to click on the named statement indicator 358 andentering the name through edit box 360.

The user can select on the back button 332 to return to the previouswindow. The "Done" button 336 is enabled during the selection processwhich terminates the current session of the find assistant and executethe resulting query.

Flow Control

FIG. 8A is a flow diagram illustrating the logical operation of thepresent invention. The process begins with the computer 106 presenting awindow 124 on the display 104. This is represented by block 500 in FIG.8A. In one embodiment, the window 124 is the condition tab window 318depicted in FIG. 3. Next, the computer 106 accepts user-selectedcommands from the user input device such as the keyboard 108 and mousedevice 110. This process is depicted by block 502. The user-selectedcommands are selected from a group comprising a database table dropdownlistbox 320 having a list of database tables, the associated databasefield listbox 322, a relational condition listbox 324, and a searchstring edit box 326. After the user makes the selection, the command isinterpreted as depicted in blocks 504 and 506, and a natural languagetranslation of the command is presented to the user on the display 104.This allows the user to select commands from a menu structure andimmediately see the result in plain language on the same display. Theforegoing process is completed until, as shown in block 508, allcommands have been accepted, and an SQL query has been defined.

If no additional search criteria are desired, block 510 presents the SQLwindow 356 to the user to allow manual editing of the SQL command. Ifadditional search criteria are required, a second condition window 338is presented to the user on the display 104. This is depicted in block514.

FIG. 8B illustrates the remainder of the process described above. Afterpresenting a second condition window 338 on the display device 104, thecomputer 106 accepts a user-selected logical condition to define thelogical relationship between the previous search criteria and theadditional criteria about to be defined by the user. This requires thatthe user input a logical conditional operator such as an "or" whichretrieves more data records, or an "and" which retrieves less datarecords. This process is depicted in block 516. If a logical conditionaloperator is not supplied, block 518 prompts interrupts processing, andprompts the user to enter a logical conditional operator. This processis visually depicted in FIG. 4, where the user selects the logicalconditional operator using radio buttons 340.

After the logical conditional operator is supplied, processing returnsto block 502, and the computer 106 accepts additional user-selectedcommand information. This information will include, for example, asecond database table, a second associated database field, a secondrelational conditional operator, and a second user-defined searchstring. As before, this information is interpreted by block 504, and anatural language translation of the search request is presented to theuser on the display device as the information is entered.

FIG. 8C illustrates the basic steps of the process described above. Atblock 530, the system displays a window containing a card metaphor onthe display 104. At block 532, the system accepts, through the cardmetaphor, a find request comprising a find type and a find conditionentered from the data input device from the user. At block 534, the findtype and find condition are interpreted to generate a natural languagedescription of the find request, and at block 536 a natural languagedescription of the find request is presented in the display 104.

Conclusion

This concludes the description of the preferred embodiment of theinvention. The present invention discloses a method, apparatus, andarticle of manufacture for displaying database management information ona computer. The method comprises the steps of displaying a windowcontaining a card metaphor on a computer display device, accepting afind type and a find condition from a user input device coupled to thecomputer, interpreting the user-selected find request to produce anatural language description of the request, and presenting the naturallanguage description of the command to the user on the display device.

The foregoing description of the preferred embodiment of the inventionhas been presented for the purposes of illustration and description. Itis not intended to be exhaustive or to limit the invention to theprecise form disclosed. Many modifications and variations are possiblein light of the above teaching. It is intended that the scope of theinvention be limited not by this detailed description, but rather by theclaims appended hereto.

What is claimed is:
 1. A computerized method of finding information in amemory of a computer having a processor, data storage device, displaydevice, and data input device, the computer processor running a databasemanagement system having a plurality of database tables each including aplurality of associated database fields having database data, the methodcomprising the steps of:(a) displaying a window containing a cardmetaphor on the display device; (b) accepting, through the cardmetaphor, a find request comprising a find type and a find conditionentered from the data input device; (c) interpreting the find type andfind condition to generate a natural language description of the findrequest; and (d) presenting the natural language description of the findrequest in the window on the display device.
 2. The method of claim 1,wherein the card metaphor comprises a first card panel having a firstradio button for creating a new find, a second radio button for editingan existing named find, and a list of find types.
 3. The method of claim2, wherein the card metaphor comprises a second card panel having a listof the database tables, an associated database field box having a listof the associated database fields, a list of relational conditions, anda find criteria edit box.
 4. The method of claim 3, wherein the secondcard panel further comprises a condition box for displaying the naturallanguage description of the find request.
 5. The method of claim 3,wherein the second card panel further comprises a first radio button forspecifying an OR logical operator and a second radio button forspecifying an AND logical operator.
 6. The method of claim 3, whereinthe card metaphor comprises a third card panel having a list of thedatabase tables, an associated database field box having a list of theassociated database fields, and a sort fields box showing a list of theassociated database fields used as sort fields.
 7. The method of claim3, wherein the card metaphor comprises a fourth card panel having a SQLentry field for accepting an SQL database query instruction.
 8. Anapparatus for finding information with a database management systemhaving a plurality of database tables each including a plurality ofassociated database fields having database data, the apparatuscomprising:(a) a computer, comprising a processor and a memory storingthe database tables; (b) means, performed by the computer, fordisplaying a window containing a card metaphor on a display coupled tothe computer; (c) means, performed by the computer for accepting,through the card metaphor, a find request comprising a find type and afind condition from a user input device into the computer; (d) means,performed by the computer, for interpreting the find type and findcondition to generate a natural language description of the findrequest; (e) means, performed by the computer, for presenting a naturallanguage description of the find request to the user in the window onthe display.
 9. The apparatus of claim 8, wherein the card metaphorcomprises a first card panel having a first radio button for creating anew find, a second radio button for editing an existing named find, anda list of find types.
 10. The apparatus of claim 9, wherein the cardmetaphor comprises a second card panel having a list of the databasetables, an associated database field box having a list of the associateddatabase fields, a list of relational conditions, and a find criteriaedit box.
 11. The apparatus of claim 10, wherein the second card panelfurther comprises a condition box for displaying the natural languagedescription of the find request.
 12. The apparatus of claim 10, whereinthe second card panel further comprises a first radio button forspecifying an OR logical operator and a second radio button forspecifying an AND logical operator.
 13. The apparatus of claim 10,wherein the card metaphor comprises a third card panel having a list ofthe database tables, an associated database field box having a list ofthe associated database fields, and a sort fields box showing a list ofthe associated database fields used as sort fields.
 14. The apparatus ofclaim 10, wherein the card metaphor comprises a fourth card panel havinga SQL entry field for accepting an SQL database query instruction.
 15. Aprogram storage device, readable by a computer having a processor, datastorage device, display device, data input device, and a memory storinga plurality of database tables each including a plurality of associateddatabase fields having database data, tangibly embodying one or moreprograms of instructions executable by the computer to perform methodsteps of finding information in the memory of the computer, the methodcomprising the steps of:(a) displaying a window containing a cardmetaphor on the display device; (b) accepting, through the cardmetaphor, a find request comprising a find type and a find conditionentered from the data input device; (c) interpreting the find type andfind condition to generate a natural language description of the findrequest; and (d) presenting the natural language description of the findrequest in the window on the display device.
 16. The program storagedevice of claim 15, wherein the card metaphor comprises a first cardpanel having a first radio button for creating a new find, a secondradio button for editing an existing named find, and a list of findtypes.
 17. The program storage device of claim 16, wherein the cardmetaphor comprises a second card panel having a list of the databasetables, an associated database field box having a list of the associateddatabase fields, a list of relational conditions, and a find criteriaedit box.
 18. The program storage device of claim 17, wherein the secondcard panel further comprises a condition box for displaying the naturallanguage description of the find request.
 19. The program storage deviceof claim 17, wherein the second card panel further comprises a firstradio button for specifying an OR logical operator and a second radiobutton for specifying an AND logical operator.
 20. The program storagedevice of claim 17, wherein the card metaphor comprises a third cardpanel having a list of the database tables, an associated database fieldbox having a list of the associated database fields, and a sort fieldsbox showing a list of the associated database fields used as sortfields.
 21. The program storage device of claim 17, wherein the cardmetaphor comprises a fourth card panel having a SQL entry field foraccepting an SQL database query instruction.